OpenEdge Development:<br />Progress 4GL Handbook Optimistic and pessimistic locking strategies

In a traditional host-based or client/server application, you can enforce what is referred to as a pessimistic locking strategy. This means that your application always obtains an EXCLUSIVE-LOCK when it first reads any record that might be updated, to make sure that no other user tries to update the same record.

In a distributed application, this technique simply can’t work. If you read a single record or a set of records on the server, and pass them to a client session for display and possible update, your server-side session cannot easily hold locks on the records while the client is using them. When the server-side procedure ends and returns the temp-table of records to the client, the server-side record buffers are out of scope and the locks released. In addition, you would not want to maintain record locks for this kind of duration, as it would lead to likely record contention.

Instead, you need to adopt an optimistic locking strategy. This means that you always read records on the server with NO-LOCK, if they are going to be passed to another session for display or processing. When the other session updates one or more records and passes them back, presumably in another copy of the temp-table that sent the records to the client, the server-side procedure in charge of handling updates must:

Find again each updated record in the database with an EXCLUSIVE-LOCK, using its key or its RowID.

Verify that the record hasn’t been changed by another user or at least verify that the current changes do not conflict with other changes.

Assign the changes to the database record.

If another user has changed the record, then the application must take appropriate action. This might involve rejecting the new changes and passing the other changes back for display, or otherwise reconciling the two sets of changes, depending on the application logic and the nature of the data.

Using FIND CURRENT and CURRENT-CHANGED

When you have a record in a record buffer, you can re-read it from the database to see if it has changed since it was read, using the FIND CURRENT statement or, for a query, the GET CURRENT statement. You can then use the CURRENT-CHANGED function to compare the record currently in the buffer with what is in the database. This is a part of your optimistic locking strategy. The simple example that follows, saved as h-findCurrent.p, shows how the syntax works:

/* h-findCurrent.p / DEFINE FRAME CustFrame Customer.CustNum Customer.NAME FORMAT "x(12)" Customer.CreditLimit Customer.Balance. ON "GO" OF FRAME CustFrame DO: / When the user closes the frame by pressing F2, start a transaction: / DO TRANSACTION: FIND CURRENT Customer EXCLUSIVE-LOCK. IF CURRENT-CHANGED(Customer) THEN DO: MESSAGE "This record has been changed by another user." SKIP "Please re-enter your changes." VIEW-AS ALERT-BOX. DISPLAY Customer.CreditLimit Customer.Balance WITH FRAME CustFrame. RETURN NO-APPLY. / Cancel the attempted update/GO / END. / Otherwise assign the changes to the database record. / ASSIGN Customer.CreditLimit Customer.Balance. END. RELEASE Customer. END. / To start out with, find, display, and enable the record with no lock. / FIND FIRST Customer NO-LOCK. DISPLAY Customer.CustNum Customer.NAME Customer.CreditLimit Customer.Balance WITH FRAME CustFrame. ENABLE Customer.CreditLimit Customer.Balance WITH FRAME CustFrame. / Wait for the trigger condition to do the update and close the frame. */ WAIT-FOR "GO" OF FRAME CustFrame.

/* h-findCurrent.p */ 
DEFINE FRAME CustFrame Customer.CustNum Customer.NAME FORMAT "x(12)" 
                       Customer.CreditLimit Customer.Balance. 
ON "GO" OF FRAME CustFrame 
DO: /* When the user closes the frame by pressing F2, start a transaction: */ 
    DO TRANSACTION: 
        FIND CURRENT Customer EXCLUSIVE-LOCK. 
        IF CURRENT-CHANGED(Customer) THEN 
        DO: 
            MESSAGE "This record has been changed by another user." SKIP 
                    "Please re-enter your changes." VIEW-AS ALERT-BOX. 
          DISPLAY Customer.CreditLimit Customer.Balance WITH FRAME CustFrame. 
            RETURN NO-APPLY.  /* Cancel the attempted update/GO */ 
        END. 
        /* Otherwise assign the changes to the database record. */ 
        ASSIGN Customer.CreditLimit Customer.Balance. 
    END. 
    RELEASE Customer.  
END. 
/* To start out with, find, display, and enable the record with no lock. */ 
FIND FIRST Customer NO-LOCK. 
DISPLAY Customer.CustNum Customer.NAME Customer.CreditLimit Customer.Balance 
    WITH FRAME CustFrame. 
ENABLE Customer.CreditLimit Customer.Balance WITH FRAME CustFrame. 
/* Wait for the trigger condition to do the update and close the frame. */ 
WAIT-FOR "GO" OF FRAME CustFrame.

The code executed first is at the bottom of the procedure, after the trigger block. It finds a desired Customer NO-LOCK, so as to avoid lock contention, then displays it and any enabled fields for input. If the user changes the CreditLimit or Balance in the frame and presses F2, which fires the GO event for the frame, the code re-reads the same record with an EXCLUSIVE-LOCK and uses CURRENT-CHANGED to compare it with the record in the buffer. Note that because the changes haven’t been assigned to the record buffer yet, the record in the buffer and the one in the database should be the same if no one else has changed it. If someone has, then the procedure displays a message, displays the changed values, and executes a RETURN NO-APPLY to cancel the GO event. Otherwise, it assigns the changes.

The DO TRANSACTION block defines the scope of the update. The RELEASE statement after that block releases the locked record from the buffer to free it up for another user. You’ll learn more about these statements in the next chapter.

Run this procedure in both sessions. Either session can update the CreditLimit or Balance, because neither session has the record locked. One session displays it:

In the other session, update it and save the change by pressing F2:

If you try to update the same record in the first session, you see a message:

The change made by the other session is displayed and, because you now see the record as it’s saved in the database, you can now re-enter your change and save it.

In a distributed application, it’s much more difficult to manage this type of situation. In the event that a server procedure gets a modified record back from another session that has already been changed, it must somehow send a response back to that session to inform it that there is a conflict. The procedure that detects the conflict does not have access to the user interface and so cannot display a message or change values directly. This is why Progress provides SmartObjects, which handle passing data from server to client, manage record locks and record contention, and communicate messages and updates back to the client from the server transparently. But it does all this using the same Progress 4GL statements that you can use in procedures you write from scratch.

In the next two chapters, you’ll explore how to write your server-side logic so as to manage database transactions and record locks properly.